<?php
	include("Base.php");
	class WorkOrder extends Base{
		//创建工单
		public function create($order,$userType){
			$db=new DB();


			$sql1="insert into chu_workorder(";
			$sql2=") values(";
			$CreatePersonId="";
			foreach ($order as $k => $v) {
				if($k=="CreatePersonId")
					$CreatePersonId=$v;
				$sql1=$sql1.$k.",";
				$sql2=$sql2."'".$v."',";
			}
			$sql1=substr($sql1, 0,-1);
			$sql2=substr($sql2, 0,-1).")";
			$sql=$sql1.$sql2;
			//echo $sql;die;
			if($db->update($sql)){
				$this->message("添加成功!");
				//还需要添加路由信息
				$s="SELECT Id from chu_workorder where CreatePersonId=".$CreatePersonId."  order by CreateTime desc";
				$wid=$db->getOne($s);
				$wid=$wid['Id'];
				$insert ="insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$wid."','".$CreatePersonId."','创建','".Time()."')";
				$db->update($insert);
				if($userType=="客户"){
					$url=array();
					$url['redirectUrl'] ="/Line/listworkorder?lineId=".$order['LineId']."&action=orderListByLineId";
					$this->data($url);
				}else{
					$url=array();
					$url['redirectUrl'] ="/WorkOrder/overview";
					$this->data($url);
				}

				

				 include 'jpush.php';

			    $pushObj = new Jpush();
			    //组装需要的参数
			    $receive = 'all';     //全部
			    $content = '你有新的工单，工单号为'.$wid.",请注意查收";
			    $m_type = '';
			    $m_txt = '';
			    $m_time = '86400';        //离线保留时间
			 
			    //调用推送,并处理
			    $result = $pushObj->push($receive,$content,$m_type,$m_txt,$m_time);
			   /* if($result){
			        $res_arr = json_decode($result, true);
			        if(isset($res_arr['error'])){                       //如果返回了error则证明失败
			            echo $res_arr['error']['message'];          //错误信息
			            echo $res_arr['error']['code'];             //错误码
			            return false;       
			        }else{
			            //处理成功的推送......
			            echo '推送成功.....';
			            return true;
			        }
			    }else{      //接口调用失败或无响应
			        echo '接口调用失败或无响应';
			        return false;
			    }*/

				//将工单分给调度员去调度,也是往路由表添加信息
				$region_id=$order['OutletId'];//站点id
				$handler=$db->getOne("SELECT * from chu_user where region_id='".$region_id."' and role_id='5' limit 1");
				$handlerId=$handler['uid'];
				if(empty($handlerId))
				{
					$handlerId=62;
				}
				$db->update("insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$wid."','".$handlerId."','"."待调度','".Time()."')");
				
			}else{
				$this->error("添加错误!");
			}
			$db->close();
			return $this;
		}
		//更新工单状态
		public function update($order)
		{
			$db=new DB();
			//应该处理该工单的员工的Id
			$handler=$db->getOne("SELECT HandlerId from chu_routelog where Result='待处理' and WorkOrderId=".$order['Id']." order by Time desc limit 1");
			$handlerId=$handler['HandlerId'];
			if($handlerId!=$order['HandlerId'])
			{
				$this->error("没有处理权限!");
				return $this;
			}
			$update="UPDATE chu_workorder set Status='".$order['Status']."',chu_workorder.Explain='".$order['Explain']."' where Id=".$order['Id'];
			if($db->update($update))
			{
				
				//添加路由信息
				$insert ="insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$order['Id']."','".$order['HandlerId']."','".$order['Handle']."','".Time()."')";
				$db->update($insert);
				$this->message('处理成功!');
				$rs = array('redirectUrl'=>"/WorkOrder/overview");
				$this->data($rs);

			}
			else{
				$this->message('更新失败!');
			}
			$db->close();
			return $this;
		}
		//查询某人的工单列表(客户，员工用)，安卓
		public function workOrderListPhone($uid,$userType){

			$db=new DB();
			$sql="";
			if($userType=="客户")
				$sql ="SELECT * from chu_workorder where ClientId= '".$uid."' order by CreateTime desc ";
			else
				$sql ="SELECT * from chu_workorder where Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid.") order by CreateTime desc";
			$list=$db->query($sql);
			$db->close();
			return $list;
		}
		/*
		//查询某人的工单列表(客户,员工共用),这是处理过的，他们要的数据格式,供前台调用
		public function workOrderList__($uid,$userType){
			$db=new DB();
			$sql="";
			if($userType=="客户")
				$sql ="SELECT * from chu_workorder where ClientId= '".$uid."'";
			else
				$sql ="SELECT * from chu_workorder where Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid.")";
			$list=$db->query($sql);
			//处理数据集
			$data=array();
			$table=array();
			$table['checkall']=true;
			$thead=array();
			$thead['Type']="工单类型";
			$thead['CreateTime']="创建时间";
			$thead['DateTime']="截止时间";
			$thead['Status']="状态";
			$table['thead']=$thead;
			$tr=array();
			$i=1;
			foreach ($list as $value) {
				$arr1=array();
				foreach ($value as $k => $v) {
					if($k=="Id"||$k=="Type"||$k=="CreateTime"||$k=="DateTime"||$k=="Status")
					{
						if($k=="CreateTime"||$k=="DateTime")
							$arr1[$k]=date("Y/m/d H:i:s",$v);
						else
							$arr1[$k]=$v;				
					}
				}
				$tr[$i]=$arr1;
				$i=$i+1;
			}
			$table['tr']=$tr;
			$operate=array();
			$operate['detail']=true;
			$operate['route']=true;
			$operate['handle']=true;
			$operate['delete']=true;
			$operate['edit']=true;

			$table['operate']=$operate;
			$data['table']=$table;
			$data['item_count']="".($i-1);
			$db->close();
			return $data;
		}
		*/

		//查询某人的工单列表(客户,员工共用),这是处理过的，他们要的数据格式,供前台调用
		public function workOrderList($serach,$uid,$userType,$current_page,$item_everypage){
			$serachKey=$serach['searchKey'];
			$db=new DB();
			$sql="";
			$sql2="";
			$count1=0;//不需要处理的
			$count2=0;//需要处理的

			$sql_statusfilter="";
			$incontent="'',";
			if($serach['nodispatch']==1)
			{
				$incontent.="'未调度',";
			}
			if($serach['nohandle']==1)
			{
				$incontent.="'未处理',";
			}
			if($serach['handled']==1)
			{
				$incontent.="'已处理',";
			}
			
			if($incontent!="")
			{

				$incontent=substr($incontent,0,strlen($incontent)-1);
				
			}
			//$incontent="'已处理','未处理','未调度'";///test
			$sql_statusfilter.=" and Status in(".$incontent.")";


			$sql_end=" order by CreateTime desc";
			//p($serachKey);
			if(trim($serachKey)=="")
			{
			if($userType=="客户")
				$sql ="SELECT * from chu_workorder where ClientId= '".$uid."' ";
			else if($userType=="调度员"){
				$sql ="SELECT * from chu_workorder where Status !='未调度' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='待调度') ";
				$sql2 ="SELECT * from chu_workorder where Status ='未调度' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='待调度') ";

			}else if($userType=="处理员"){
				$sql ="SELECT * from chu_workorder where Status ='已处理' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='处理'  ) ";
				$sql2 ="SELECT * from chu_workorder where Status !='已处理' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='待处理' and Flag!=1 ) ";
				//p($sql."    ".$sql2);die;
			}else{
				//员工
				$sql ="SELECT * from chu_workorder where CreatePersonId= '".$uid."' ";
			}
			}
			else
			{
			if($userType=="客户")
				$sql ="SELECT * from chu_workorder where ClientId= '".$uid."' and Id like '%".$serachKey."%' ";
			else if($userType=="调度员"){
				$sql ="SELECT * from chu_workorder where Status !='未调度' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='待调度' ) and Id like '%".$serachKey."%' ";
				$sql2 ="SELECT * from chu_workorder where Status ='未调度' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='待调度' ) and Id like '%".$serachKey."%' ";

			}else if($userType=="处理员"){
				$sql ="SELECT * from chu_workorder where Status ='已处理' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='处理'   ) and Id like '%".$serachKey."%' ";
				$sql2 ="SELECT * from chu_workorder where Status !='已处理' and Id in (SELECT WorkOrderId from chu_routelog where HandlerId=".$uid." and Result='待处理' and Flag!=1 ) and Id like '%".$serachKey."%' ";
				//p($sql."    ".$sql2);die;
			}else{
				//员工
				$sql ="SELECT * from chu_workorder where CreatePersonId= '".$uid."' and Id like '%".$serachKey."%' ";
			}
			}
			$datefilter="";
			if(trim($serach['searchDateStart'])!="")
			{
				$dateStart=strtotime($serach['searchDateStart']);
				if($serach['searchDateEnd']!="")
				$dateEnd=strtotime($serach['searchDateEnd']." 23:59:59");
				else
				$dateEnd=time();
				$datefilter=" and CreateTime>=".$dateStart."&&CreateTime<".$dateEnd;
				$sql=$sql.$datefilter;
			}
			$sql=$sql.$sql_statusfilter;
			
			$sql=$sql.$sql_end;
			$list=$db->query($sql);
			//p($sql);
			$count1=count($list);
			
			if($userType!="客户"&&$userType!="员工"){
				$sql2=$sql2.$datefilter;
				$sql2=$sql2.$sql_statusfilter;
				$sql2=$sql2.$sql_end;
				//p($sql2);die;
				$list2=$db->query($sql2);//待处理的
				$count2=count($list2);
				$count1+=$count2;
				$list=array_merge($list2,$list);
			}
			
			

			//处理数据集
			$data=array();
			$table=array();
			$table['checkall']=true;
			$thead=array();


			$thead['Id']="工单Id";

			$thead['Type']="工单类型";
			$thead['CreateTime']="创建时间";
			$thead['DateTime']="截止时间";
			$thead['Status']="状态";
			$table['thead']=$thead;
			$tr=array();
			
			$i=1;
			$start=($current_page-1)*$item_everypage+1;
			if($count1-$start+1<$item_everypage)
			{
				$end=$count1;
			}
			else
			{
				$end=$start+$item_everypage-1;
			}
			
			//p($start."        ".$end."   ".$count1." current_page ".$current_page." item_everypage  ".$item_everypage);die;
			foreach ($list as $value) {
				if($i>=$start&&$i<=$end){
					$arr1=array();
					foreach ($value as $k => $v) {
						if($k=="Id"||$k=="Type"||$k=="CreateTime"||$k=="DateTime"||$k=="Status")
						{
							if($k=="CreateTime"||$k=="DateTime")
								$arr1[$k]=date("Y/m/d H:i:s",$v+8*60*60);
							else
								$arr1[$k]=$v;				
						}
					}


					if($arr1['Status']=="已处理"){
					
					$arr1['commentNew'] = true; //新添加
					$sql ="SELECT * FROM chu_comment WHERE WorkOrderId =".$arr1['Id'];
					if($db->getOne($sql)){
						$arr1['commentView'] = true; //已评论，可查看
					};
					
					}
					$tr[$i]=$arr1;
				}
				$i=$i+1;
				if($i>$end)
					break;
			}
			
			$table['tr']=$tr;
			
			$operate=array();
			
			$operate['detail']=true;
			$operate['route']=true;
			$operate['handle']=false;
			$operate['delete']=true;
			$operate['edit']=true;
			$table['operate1']=$operate;

			$table['userType']=$userType;
			

			
			$data['table']=$table;
			$data['item_count']="".($count1);
			
			$db->close();
			//p($data);die;
			return $data;
		}
		
		
		public function clientList($search,$userType,$region_id,$current_page,$item_everypage)
		{
			
			// $search['searchKey']=getdata('searchKey');
			// $search['searchDateStart']=getdata('searchDateStart');
			// $search['searchDateEnd']=getdata('searchDateEnd');
			$sql_limit=" LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			$sql="select uid,uname,email from chu_user where Type='客户'";
			$sql_count="select count(*) as count from chu_user  where Type='客户'";
			$sql_filterRegion="";
			$sql_filtername="";
			$sql_datefilter="";
			if($userType=="员工")
			{
				$sql_filterRegion=" and region_id=".$region_id;//员工获取相同区域的
				$sql.=$sql_filterRegion;
				$sql_count.=$sql_filterRegion;
			}
			if(trim($search['searchKey'])!="")
			{
				$sql_filtername=" and uname like '%".$search['searchKey']."%'";
				$sql.=$sql_filtername;
				$sql_count.=$sql_filtername;
			}
			
			$sql.=$sql_limit;			
			$db=new DB();
			$list=$db->query($sql);
			$count=$db->query($sql_count);
			
			$data=array();
			
			$table=array();
			$table['checkall']=true;
			$thead=array();
			$thead['Id']="用户ID";
			$thead['name']="用户名";
			$thead['email']="邮箱";
			$table['thead']=$thead;
			
			$tr=array();
			
			$i=1;
			foreach($list as $value)
			{
				$tr_item=array();
				foreach($value as $k=>$v)
				{
					$tr_item[$k]=$v;
				}
				$tr[$i]=$tr_item;
				$i++;
			}
			
			$table['tr']=$tr;
			
			$operate=array();
			$operate['detail']=true;
			$table['operate']=$operate;
			
			
			$data['table']=$table;
			$data['item_count']=$count[0]['count'];
			$db->close();
			
			
			return $data;
			

			
		}
		public function workOrderListByLineId($lineId,$current_page,$item_everypage){
			$db=new DB();


			$sql ="SELECT * from chu_workorder where LineId=".$lineId." order by CreateTime desc";

			$list=$db->query($sql);
			//处理数据集
			$data=array();
			$table=array();
			$table['checkall']=true;
			$thead=array();
			$thead['Id']="工单Id";
			$thead['Type']="工单类型";
			$thead['CreateTime']="创建时间";
			$thead['DateTime']="截止时间";
			$thead['Status']="状态";
			$table['thead']=$thead;
			$tr=array();
			
			$i=1;
			$start=($current_page-1)*$item_everypage+1;
			$end=$start+$item_everypage-1;
			foreach ($list as $value) {
				if($i>=$start&&$i<=$end){
					$arr1=array();
					foreach ($value as $k => $v) {
						if($k=="Id"||$k=="Type"||$k=="CreateTime"||$k=="DateTime"||$k=="Status")
						{
							if($k=="CreateTime"||$k=="DateTime")
								$arr1[$k]=date("Y/m/d H:i:s",$v+8*60*60);
							else
								$arr1[$k]=$v;				
						}
					}

					if($arr1['Status']=="已处理"){
						
						$arr1['commentNew'] = true; //新添加
						$sql ="SELECT * FROM chu_comment WHERE WorkOrderId =".$arr1['Id'];
						if($db->getOne($sql)){
							$arr1['commentNew'] = false;
							$arr1['commentView'] = true; //已评论，可查看
						};
						
					}
					$tr[$i]=$arr1;
				}
				
				$i=$i+1;
				if($i>$end)
					break;
			}
			
			$table['tr']=$tr;
			
			$operate=array();
			
			$operate['detail']=true;
			$operate['route']=true;
			$operate['handle']=false;
			$operate['delete']=true;
			$operate['edit']=true;
			$table['operate']=$operate;
			
			$data['table']=$table;
			
			
			

			$data['item_count']="".count($list);

			$db->close();
			//p($data);die;
			return $data;
		}
		
		public function workOrderListByLineId_temp($lineId){
			$db=new DB();
			$sql ="SELECT * from chu_workorder where LineId=".$lineId." order by CreateTime desc ";
			$list=$db->query($sql);
			//处理数据集
			$data=array();
			$table=array();
			$table['checkall']=true;
			$thead=array();
			$thead['Type']="工单类型";
			$thead['CreateTime']="创建时间";
			$thead['DateTime']="截止时间";
			$thead['Status']="状态";
			$table['thead']=$thead;
			$tr=array();
			
			$i=1;
			foreach ($list as $value) {
				$arr1=array();
				foreach ($value as $k => $v) {
					if($k=="Id"||$k=="Type"||$k=="CreateTime"||$k=="DateTime"||$k=="Status")
					{
						if($k=="CreateTime"||$k=="DateTime")
							$arr1[$k]=date("Y/m/d H:i:s",$v+8*60*60);
						else
							$arr1[$k]=$v;				
					}
				}

				if($arr1['Status']=="已处理"){
					
					$arr1['commentNew'] = true; //新添加
					$sql ="SELECT * FROM chu_comment WHERE WorkOrderId =".$arr1['Id'];
					if($db->getOne($sql)){
						$arr1['commentNew'] = false;
						$arr1['commentView'] = true; //已评论，可查看
					};
					
				}
				$tr[$i]=$arr1;
				$i=$i+1;
			}
			
			$table['tr']=$tr;
			
			$operate=array();
			
			$operate['detail']=true;
			$operate['route']=true;
			$operate['handle']=false;
			$operate['delete']=true;
			$operate['edit']=true;
			$table['operate']=$operate;
			
			$data['table']=$table;
			
			
			
			$data['item_count']="".($i-1);;
			$db->close();
			//p($data);die;
			return $data;
		}
		
		//查询工单详情
		public function workOrderDetailById($wid){
			$db=new DB();
			$sql ="SELECT * from chu_workorder where Id=".$wid;

			$order=$db->getOne($sql);
			$CreatePersonId=$db->getOne("SELECT uname from chu_user where uid=".$order['CreatePersonId']);
			$order['CreatePersonId']=$CreatePersonId['uname'];
			$ClientId=$db->getOne("SELECT uname from chu_user where uid=".$order['ClientId']);
			$order['ClientId']=$ClientId['uname'];
			$order['OutletId']=getOutlet($order['OutletId'],$db);
			//$order['OutletId']=1111;
			//p($order['OutletId']);die;
			$db->close();
			return $order;
		}
		//查询工单详情
		public function workOrderDetailById__($wid){
			$db=new DB();
			$sql ="SELECT * from chu_workorder where Id=".$wid;
			$order=$db->getOne($sql);
			$db->close();
			return $order;
		}
		//查询工单的路由信息
		public function routeLogById__($wid){
			$db=new DB();
			$sql ="SELECT * from chu_routelog where WorkOrderId='".$wid."' order by Time";
			$list=$db->query($sql);

			$db->close();
			return $list;
		}
		//查询工单的路由信息
		public function routeLogById($wid){
			$db=new DB();
			$sql ="SELECT chu_routelog.Id,chu_routelog.WorkOrderId,chu_user.uname HandlerId,chu_routelog.Result, chu_routelog.Time from chu_routelog ,chu_user where chu_routelog.HandlerId=chu_user.uid and chu_routelog.WorkOrderId='".$wid."' order by Time";
			//p($sql);die;
			$list=$db->query($sql);

			$db->close();
			return $list;
		}
		//初始化工单信息(将可以申请的工单类型和省给前台)
		public function initWorkOrder($userType,$region_id){
			$data=array();
			if($userType=="客户"){
				$data['workorderType']=array("线路中断","线路时延","宽带故障");
			}
			else{
				$db=new DB();
				$data['workorderType']=array("线路中断","线路时延","宽带故障","环保护失效","单板失效");
				$data['clients']=$db->query("SELECT * from chu_user where Type='客户' and region_id=".$region_id);
				if(count($data['clients'])){
					$data['lines']=$db->query("SELECT * from chu_line where ApplyerId=".$data['clients'][0]['uid']);
				}
			}
			$db=new DB();
			$data['province']=getRegion(1111,1);

			$db->close();
			return $data;
		}
		public function initWorkOrderByphone($userType){
			$data=array();
			if($userType=="客户"){
				$data['workorderType']=array("1"=>"线路中断","2"=>"线路时延","3"=>"宽带故障");
			}
			else{
				$db=new DB();
				$data['workorderType']=array("1"=>"线路中断","2"=>"线路时延","3"=>"宽带故障","4"=>"环保护失效","5"=>"单板失效");
				$data['clients']=$db->query("SELECT * from chu_user where Type='客户'");
				if(count($data['clients'])){
					$data['lines']=$db->query("SELECT * from chu_line where ApplyerId=".$data['clients'][0]['uid']);
				}
			}
			$db=new DB();
			$data['province']=getRegion(1111,1);

			$db->close();
			return $data;
		}
		//客户通过uid得到自己的线路列表
		public function getLineListByUid__($uid){
			$db=new DB();
			$sql="SELECT * from chu_line where ApplyerId=".$uid;
			$data=$db->query($sql);
			$db->close();
			return $data;
		}

		//客户通过uname得到自己的线路列表
		public function getLineListByUname($uname,$current_page,$item_everypage){
			$db=new DB();
			$sql="SELECT chu_line.Id,chu_line.Name, chu_user.uname  from chu_line,chu_user where chu_user.uname='".$uname."' and chu_line.ApplyerId=chu_user.uid LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			$sql_count="select count(*) as count from chu_line,chu_user where chu_user.uname='".$uname."' and chu_line.ApplyerId=chu_user.uid";
			//p($sql);die;
			$list=$db->query($sql);
			$count=$db->query($sql_count);
			//处理数据集
			$data=array();
			$table=array();
			$table['checkall']=true;
			$thead=array();
			$thead['Id']="线路Id";
			$thead['Name']="线路名";
			$thead['uname']="申请人";
			$table['thead']=$thead;
			$tr=array();
			
			// $i=1;
			// $start=($current_page-1)*$item_everypage+1;
			// $end=$start+$item_everypage-1;
			// foreach ($list as $value) {
			// 	if($i>=$start&&$i<=$end){
			// 		$arr1=array();
			// 		foreach ($value as $k => $v) {
			// 			$arr1[$k]=$v;				
			// 		}
			// 		$tr[$i]=$arr1;
			// 	}
			// 	$i=$i+1;
			// 	if($i>$end)
			// 		break;
			// }
			
						$i=1;
			foreach ($list as $value) {
				$arr1=array();
				foreach ($value as $k => $v) {
					$arr1[$k]=$v;				
				}
				$tr[$i]=$arr1;
				$i=$i+1;
			}
			
			$table['tr']=$tr;
			
			$operate=array();
			
			$operate['detail']=true;
			$operate['createworkorder']=true;
			$operate['workorderinfo']=true;
			$table['operate']=$operate;
			
			$data['table']=$table;

			$data['item_count']="".$count[0]['count'];

			$db->close();
			//p($data);die;
			return $data;
		}

		public function getLineListByManngerId($uid,$region_id,$current_page,$item_everypage)
		{
			$db=new DB();
			$region=$db->getOne("SELECT region_type from chu_region where region_id=".$region_id);
			$region_type=$region['region_type'];
			$sql="";
			$sql_count="";
			if($region_type==3){
				$sql="SELECT chu_line.Id,chu_line.Name, chu_user.uname ,chu_user.Type from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_user.region_id=".$region_id." LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="SELECT count(*) as count from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_user.region_id=".$region_id;
			}
			if($region_type==2){
				$sql="SELECT chu_line.Id,chu_line.Name, chu_user.uname ,chu_user.Type from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_user.region_id in (SELECT  region_id from chu_region where region_id=".$region_id." or parent_id=".$region_id.") LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="SELECT count(*) as count from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_user.region_id in (SELECT  region_id from chu_region where region_id=".$region_id." or parent_id=".$region_id.")";
			}
			if($region_type==1){
				$sql="SELECT chu_line.Id,chu_line.Name, chu_user.uname ,chu_user.Type from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_user.region_id in (SELECT  region_id from chu_region  where region_id=".$region_id." or parent_id=".$region_id."  union (select region_id from chu_region where parent_id in (select region_id from chu_region where parent_id=".$region_id."))) LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="SELECT count(*) as count from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_user.region_id in (SELECT  region_id from chu_region  where region_id=".$region_id." or parent_id=".$region_id."  union (select region_id from chu_region where parent_id in (select region_id from chu_region where parent_id=".$region_id.")))";
			}
			$list=$db->query($sql);
			$count=$db->query($sql_count);
			//处理数据集
			$data=array();
			$table=array();
			$table['checkall']=true;
			$thead=array();
			$thead['Id']="线路Id";
			$thead['Name']="线路名";
			$thead['uname']="客户名";
			$table['thead']=$thead;
			$tr=array();
			
			$i=1;
			foreach ($list as $value) {
				$arr1=array();
				foreach ($value as $k => $v) {
					$arr1[$k]=$v;				
				}
				$tr[$i]=$arr1;
				$i=$i+1;
			}
			
			$table['tr']=$tr;
			
			$operate=array();
			
			$operate['detail']=true;
			$operate['createworkorder']=true;
			$operate['workorderinfo']=true;
			$table['operate']=$operate;
			
			$data['table']=$table;
			$data['item_count']="".$count[0]['count'];
			$db->close();
			//p($data);die;
			return $data;
			
			
		}

		public function getLineRank($uid){
			$db=new DB();
			//$sql="select chu_line.Name,COUNT(*) Count from chu_workorder,chu_line WHERE chu_line.Id=chu_workorder.LineId and  chu_workorder.LineId in(SELECT Id from chu_line where ApplyerId=".$uid.") GROUP BY chu_line.Name";
			$sql="select chu_line.Name,chu_line.Describe,count(LineId) Count from chu_line LEFT JOIN   chu_workorder ON chu_line.Id=chu_workorder.LineId WHERE chu_line.Id in(SELECT Id from chu_line where ApplyerId=".$uid.") GROUP BY chu_line.Name order by Count desc";
			$rank=$db->query($sql);
			$name=array();
			$count=array();
			foreach ($rank as $k => $v) {
				$name[]=$v['Name'];
				$count[]=$v['Count'];
			}

			$title=array("text"=>"故障数");

			$xAxis=array();
			$x=array();
			$x['type']="category";
			$x['boundaryGap']=false;
			$x['data']=$name;
			$xAxis[]=$x;


			$yAxis=array();
			$y=array();
			$y['type']="value";
			$axisLabel=array("formatter"=>"{value}");
			$y['axisLabel']=$axisLabel;
			$yAxis[]=$y;

			$series=array();
			$s=array();
			$s["name"]="故障数";
			$s["type"]="line";
			$s["data"]=$count;
			$s['markPoint']=array("data"=>array(array("type"=>"max","name"=>"最大值"),array("type"=>"min","name"=>"最小值")));
			$series[]=$s;

			$option=array("title"=>$title,"xAxis"=>$xAxis,"yAxis"=>$yAxis,"series"=>$series);
			$Line=array("option"=>$option);
			$chart=array("Line"=>$Line);
			$table=$this->getLineRankTable($uid);
			$data=array("chart"=>$chart,"table"=>$table);
			$db->close();
			return $data;
		}

			//初始化新评论@scott
			public function initComment($id){
				$db=new DB();
				$sql ="SELECT Id,CreatePersonId,ClientId,LineId FROM chu_workorder WHERE Id =".$id;
				$info=$db->getOne($sql);
				$sql1 = "SELECT uname FROM chu_routelog  INNER JOIN chu_user WHERE HandlerId = uid AND Result='处理' AND WorkOrderId =".$id;
				$info1=$db->getOne($sql1);
				$commentInfo = array();
				$commentInfo['workOrderId'] = $id;
				$commentInfo['handler'] = $info1['uname'];

				$sql2 = "SELECT NAME FROM chu_line WHERE Id =".$info['LineId'];
				$info1=$db->getOne($sql2);
				$commentInfo['lineName'] = $info1['NAME'];
				$commentInfo['lineId'] = $info['LineId'];
						   
				$db->close();
				return $commentInfo;
			}
			//新评论提交@scott
			public function commentSubmit($workOrderId,$score,$description){
				$db=new DB();
				$commentInfo = array();
				$insert ="insert into chu_comment(Score,Context,WorkOrderId) value('".$score."','".$description."','".$workOrderId."')";
				if($db->update($insert)){
					$this->message("评论成功!");
					$sql ="SELECT Id,CreatePersonId,ClientId,LineId FROM chu_workorder WHERE Id =".$workOrderId;
					$info=$db->getOne($sql);
					$commentInfo['workOrderId'] = $workOrderId;
					$commentInfo['score'] = $score;
					$commentInfo['description'] = $description;
					$commentInfo['redirectUrl'] = "/Line/listworkorder?lineId=".$info['LineId']."&action=orderListByLineId";
				
				}else{
					$this->error("添加评论失败");
				}
				$db->close();
				return $commentInfo;
			}
			//查看评论@scott
			public function viewComment($id){
				$db=new DB();
				$sql ="SELECT Id,CreatePersonId,ClientId,LineId FROM chu_workorder WHERE Id =".$id;
				$info=$db->getOne($sql);
				$sql1 = "SELECT uname FROM chu_routelog  INNER JOIN chu_user WHERE HandlerId = uid AND Result='处理' AND WorkOrderId =".$id;
				$info1=$db->getOne($sql1);
				$commentInfo = array();
				$commentInfo['workOrderId'] = $id;
				$commentInfo['handler'] = $info1['uname'];
				$sql2 = "SELECT NAME FROM chu_line WHERE Id =".$info['LineId'];
				$info1=$db->getOne($sql2);
				$commentInfo['lineName'] = $info1['NAME'];
				$commentInfo['lineId'] = $info['LineId'];
				$sql2 = "SELECT * FROM chu_comment WHERE WorkOrderId =".$id;
				$info1=$db->getOne($sql2);
				$commentInfo['context'] = $info1['Context'];
				$commentInfo['score'] = $info1['Score'];

				for( $i = 0;$i < 5;$i++){
					if($i < $info1['Score']){
						$commentInfo['images'][$i] = "s1.gif";
					}else{
						$commentInfo['images'][$i] = "s0.gif";
					};
				}
				$commentInfo['image_1'] = $commentInfo['images'][0];
				$commentInfo['image_2'] = $commentInfo['images'][1];
				$commentInfo['image_3'] = $commentInfo['images'][2];
				$commentInfo['image_4'] = $commentInfo['images'][3];		   
				$commentInfo['image_5'] = $commentInfo['images'][4];

				$db->close();
				return $commentInfo;
			}


		public function getLineRankTable($uid){
			$db=new DB();
			$sql="select chu_line.Id, chu_line.Name,chu_line.Describe,count(LineId) Count from chu_line LEFT JOIN   chu_workorder ON chu_line.Id=chu_workorder.LineId WHERE chu_line.Id in(SELECT Id from chu_line where ApplyerId=".$uid.") GROUP BY chu_line.Name order by count(LineId) desc";
			$rank=$db->query($sql);
			
			$thead=array();
			$thead['Rank']="排名";
			$thead['Name']="线路名";
			$thead['Count']="故障工单数";
			$tr=array();
			$i=1;
			foreach ($rank as $k => $v) {
				$t=array();
				$t["Id"]=$v["Id"];
				$t["Rank"]=$i;
				$t["Name"]=$v["Name"];
				$t["Count"]=$v["Count"];
				$t["Describe"]=$v["Describe"];
				$tr[]=$t;
				$i=$i+1;
			}
			$table=array("thead"=>$thead,"tr"=>$tr);
			$db->close();
			return $table;
		}
		public function getContact($uid,$userType,$userRegionId){
			$db=new DB();
			$sql="";
			if($userType=="客户"){
				$sql="select DISTINCT chu_user.uname name from chu_routelog,chu_user where chu_routelog.HandlerId=chu_user.uid and chu_user.uid !=".$uid." and chu_routelog.WorkOrderId in (select Id from chu_workorder where ClientId=".$uid.")";
			}else if($userType=="员工"){
				$sql="select uname name from chu_user where region_id=".$userRegionId." and Type ='员工' and uid!=".$uid;
			}else if($userType=="区管理员")
			{
				$sql="select uname name from chu_user where region_id=".$userRegionId." and Type !='客户' and uid!=".$uid;
			}
			else if($userType=="处理员")
			{
				$sql="select uname name from chu_user where region_id=".$userRegionId." and Type !='客户' and uid!=".$uid." or (uid in(select DISTINCT(ClientId) from chu_workorder  where Id in(
 select DISTINCT(WorkOrderId) from chu_routelog where HandlerId=".$uid.")))";
 			//echo $sql;
			}
			$contract=$db->query($sql);
			$db->close();
			return $contract;
		}

		public function getNoticeList($type,$region_id,$current_page,$item_everypage){
			
			
			if($type=="客户")
			{
				$operate=array("detail"=>true);
			}
			else
			{
				$operate=array("detail"=>true,"delete"=>true);
			}
			$sql="SELECT * from chu_notice where Region_id =".$region_id." order by CreateTime desc LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			$sql_count="SELECT count(*) as count from chu_notice";
			//p($sql);die;
			$db=new DB();
			$notices=$db->query($sql);
			$count=$db->query($sql_count);
		//	p($count); die;
			$thead=array();
			$thead['Title']="主题";
			$thead['Context']="发布时间";
			$tr=array();
			foreach ($notices as $k => $v) {
				$t=array();
				$t["Id"]=$v["Id"];
				$t["Title"]=$v["Title"];
				$t["Context"]=$v["Context"];
				$t["CreateTime"]=$v["CreateTime"];
				$t["DateTime"]=$v["DateTime"];
				$t["Region_id"]=$v["Region_id"];
				$tr[]=$t;
			}
			
			$table=array("thead"=>$thead,"tr"=>$tr,"operate"=>$operate);
			$data=array();
			$data['table']=$table;
			$data['item_count']="".$count[0]["count"];
			$db->close();
			return $data;

		}

		public function getNoticeDetail($id){
			$db=new DB();
			$sql="SELECT * from chu_notice where Id=".$id;
			$detail=$db->getOne($sql);
			$db->close();
			return $detail;
		}

		public function getWarningList($userId,$userType,$region_id,$current_page,$item_everypage){
			$db=new DB();
			if($userType=="客户")
			{
			$sql="select * from chu_warning where EXISTS (select * from chu_line where chu_warning.LineNo=chu_line.`Name` and chu_line.ApplyerId=".$userId.") ORDER BY WarningLevelNo DESC LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			
			$sql_count="select count(*) as count from chu_warning where EXISTS (select * from chu_line where chu_warning.LineNo=chu_line.`Name` and chu_line.ApplyerId=".$userId.")";
			$operate=array("detail"=>true);
			}
			else
			{
			$sql="select * from chu_warning where chu_warning.Region_id=".$region_id." ORDER BY WarningLevelNo DESC LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			$sql_count="select count(*) as count from chu_warning where chu_warning.Region_id=".$region_id;
			$operate=array("detail"=>true,"delete"=>true);	
			}
			//$sql="SELECT * from chu_warning where WarningObject ='".$userType."' and Region_id=".$region_id." ORDER BY WarningLevelNo DESC LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage ;
			//$sql="select * from chu_warning where EXISTS (select * from chu_line where chu_warning.LineNo=chu_line.`Name` and chu_line.ApplyerId=".$userId.") ORDER BY WarningLevelNo DESC LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			//$sql_count="select count(*) as count from chu_warning where WarningObject ='".$userType."' and Region_id=".$region_id;
			//$sql_count="select count(*) as count from chu_warning where EXISTS (select * from chu_line where chu_warning.LineNo=chu_line.`Name` and chu_line.ApplyerId=".$userId.")";
			//p($sql);die;
			$warnings=$db->query($sql);
			$count=$db->query($sql_count);
			$thead=array();
			$thead['LineNo']="电路编号";
			$thead['WarningLevel']="预警级别";
			$thead['WarningTime']="预警时间";
			$tr=array();
			foreach ($warnings as $k => $v) {
				$t=array();
				$t["Id"]=$v["Id"];
				$t["LineNo"]=$v["LineNo"];
				$t["WarningLevel"]=$v["WarningLevel"];
				$t["WarningTime"]=$v["WarningTime"];
				$t["WarningObject"]=$v["WarningObject"];
				$t["WarningDetail"]=$v["WarningDetail"];
				$t["Region_id"]=$v["Region_id"];
				$tr[]=$t;
			}
			
			$table=array("thead"=>$thead,"tr"=>$tr,"operate"=>$operate);
			$data['table']=$table;
			$data['item_count']="".$count[0]["count"];
			$db->close();
			return $data;
		}

		public function getWarningDetail($id){
			$db=new DB();
			$sql="SELECT * from chu_warning where Id=".$id;
			$detail=$db->getOne($sql);
			$db->close();
			return $detail;
		}
		public function getClientDetail($id){
			$db=new DB();
			$sql="select * from chu_user where uid=".$id;
			$detail=$db->getOne($sql);
			$detail['region_id']=getOutlet($detail['region_id'],$db);
			//p($sql); die;
			$db->close();
			return $detail;
		}

		//得到图片列表
		public function getImageList($type){
			$db=new DB();
			$sql="SELECT * from chu_image where ImageType='".$type."' order by Sort";
			$images=$db->query($sql);
			$db->close();
			return $images;
		}
		

		//创建告警
		public function createWarning($arr,$CreatePersonId){
			if($arr["WarningLevel"]=="高")
				$arr['WarningLevelNo']=3;
			if($arr["WarningLevel"]=="中")
				$arr['WarningLevelNo']=2;
			if($arr["WarningLevel"]=="低")
				$arr['WarningLevelNo']=1;
			$db=new DB();
			$sql1="insert into chu_warning(";
			$sql2=") values(";
			foreach ($arr as $k => $v) {
				$sql1=$sql1.$k.",";
				$sql2=$sql2."'".$v."',";
			}
			$sql1=substr($sql1, 0,-1);
			$sql2=substr($sql2, 0,-1).")";
			$sql=$sql1.$sql2;
			//p($sql);die;
			if($db->update($sql))
			{
				$this->message("告警成功!");
				//告警级别高就需要生成工单
				if($arr['WarningLevel']=="高"){
					$line=$db->getOne("SELECT chu_line.Id ,chu_line.ApplyerId,chu_user.Region_id from chu_line,chu_user where chu_user.uid=chu_line.ApplyerId and  Name='".$arr['LineNo']."'");
					$array=array();
					$array['CreatePersonId']=$CreatePersonId;
					$array['lineId']=$line['Id'];
					$array['ClientId']=$line['ApplyerId'];
					$array['Type']='线路中断';
					$array['Context']=$arr['WarningDetail'];
					$array['OutletId']=$line['Region_id'];
					$array['Status']="未调度";
					$array['CreateTime']=Time();
					$array['DateTime']=Time()+60*60*24*1;//时间转换时间戳
					$this->create($array,"员工");
					$this->message("告警成功!,已经创建工单!");
				}
			}
			else
				$this->error("告警失败!");
			return $this;
		}
		//删除告警
		public function deleteWarning($Id){
			$db=new DB();
			$sql="delete from chu_warning where Id=".$Id;
			//p($sql);die;
			if($db->update($sql))
			{
				$this->message("删除成功!");
			}
			else
				$this->error("告警失败!");
			return $this;
		}


		//得到线路告警排名表
		public function getLineWarningRankTable($uid){
			$db=new DB();
			$sql="select chu_line.Name,chu_line.Describe,COUNT(chu_warning.LineNo) Count from chu_line LEFT JOIN   chu_warning ON chu_line.Name=chu_warning.LineNo WHERE chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.") GROUP BY chu_line.Name ORDER BY COUNT(chu_warning.LineNo) desc";
			$rank=$db->query($sql);
			
			$thead=array();
			$thead['Rank']="排名";
			$thead['Name']="线路名";
			$thead['Count']="告警数";
			$tr=array();
			$i=1;
			foreach ($rank as $k => $v) {
				$t=array();
				$t["Rank"]=$i;
				$t["Name"]=$v["Name"];
				$t["Count"]=$v["Count"];
				$t["Describe"]=$v["Describe"];
				$tr[]=$t;
				$i=$i+1;
			}
			$table=array("thead"=>$thead,"tr"=>$tr);
			$db->close();
			return $table;
		}
		//得到线路告警排名（有echar和table）
		public function getLineWaringRank($uid){
			$db=new DB();
			//$sql="select chu_line.Name,COUNT(*) Count from chu_workorder,chu_line WHERE chu_line.Id=chu_workorder.LineId and  chu_workorder.LineId in(SELECT Id from chu_line where ApplyerId=".$uid.") GROUP BY chu_line.Name";
			$sql="select chu_line.Name,chu_line.Describe,COUNT(chu_warning.LineNo) Count from chu_line LEFT JOIN   chu_warning ON chu_line.Name=chu_warning.LineNo WHERE chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.") GROUP BY chu_line.Name ORDER BY COUNT(chu_warning.LineNo) desc";
			$rank=$db->query($sql);
			$name=array();
			$count=array();
			foreach ($rank as $k => $v) {
				$name[]=$v['Name'];
				$count[]=$v['Count'];
			}

			$title=array("text"=>"告警数");

			$xAxis=array();
			$x=array();
			$x['type']="category";
			$x['boundaryGap']=false;
			$x['data']=$name;
			$xAxis[]=$x;


			$yAxis=array();
			$y=array();
			$y['type']="value";
			$axisLabel=array("formatter"=>"{value}");
			$y['axisLabel']=$axisLabel;
			$yAxis[]=$y;

			$series=array();
			$s=array();
			$s["name"]="告警数";
			$s["type"]="line";
			$s["data"]=$count;
			$s['markPoint']=array("data"=>array(array("type"=>"max","name"=>"最大值"),array("type"=>"min","name"=>"最小值")));
			$series[]=$s;

			$option=array("title"=>$title,"xAxis"=>$xAxis,"yAxis"=>$yAxis,"series"=>$series);
			$Line=array("option"=>$option);
			$chart=array("Line"=>$Line);
			$table=$this->getLineWarningRankTable($uid);
			$data=array("chart"=>$chart,"table"=>$table);
			$db->close();
			return $data;
		}

		//得到级别排名表
		public function getWarningLevelRank($uid,$current_page,$item_everypage){
			$db=new DB();
			$sql="select chu_line.Name,chu_line.Describe,chu_warning.WarningLevel Level from chu_line ,chu_warning WHERE chu_line.Name=chu_warning.LineNo and  chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.")  ORDER BY chu_warning.WarningLevelNo DESC LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			$sql_count="select count(*) as count from chu_line ,chu_warning WHERE chu_line.Name=chu_warning.LineNo and  chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.") ";
			$rank=$db->query($sql);
			$count=$db->query($sql_count);
			$thead=array();
			$thead['Rank']="排名";
			$thead['Name']="线路名";
			$thead['Level']="告警级别";
			$tr=array();
			$i=($current_page-1)*$item_everypage+1;
			foreach ($rank as $k => $v) {
				$t=array();
				$t["Rank"]=$i;
				$t["Name"]=$v["Name"];
				$t["Level"]=$v["Level"];
				$t["Describe"]=$v["Describe"];
				$tr[]=$t;
				$i=$i+1;
			}
			$table=array("thead"=>$thead,"tr"=>$tr);
			$data=array("table"=>$table);
			$data['item_count']="".$count[0]["count"];
			$db->close();
			return $data;
		}
		//得到流量统计表
		public function getTrafficTable($region_id,$Type,$uid,$current_page,$item_everypage){
			$db=new DB();

			$operate=array("delete"=>true);
			if($Type!="客户")//目前是获得所有的告警
			{
				$sql="select chu_line.Name,chu_line.Describe,chu_trafficwarning.MinTraffic,chu_trafficwarning.MaxTraffic,chu_trafficwarning.Id from chu_line ,chu_trafficwarning WHERE chu_line.Name=chu_trafficwarning.LineNo 
						
						and chu_trafficwarning.LineNo in (select Name from chu_line WHERE ApplyerId in (select uid from 
						
						chu_user where region_id=".$region_id.")) LIMIT ".($current_page-1)*$item_everypage.",".
						
						$item_everypage;
	
				$sql_count="select count(*) as count from chu_line ,chu_trafficwarning 

					WHERE chu_line.Name=chu_trafficwarning.LineNo and chu_trafficwarning.LineNo in (select Name from 
					
					chu_line WHERE ApplyerId in (select uid from chu_user where region_id=".$region_id."))";
			}
			else
			{
				$sql="select chu_line.Name,chu_line.Describe,chu_trafficwarning.MinTraffic,chu_trafficwarning.MaxTraffic,chu_trafficwarning.Id from chu_line ,chu_trafficwarning WHERE chu_line.Name=chu_trafficwarning.LineNo and  chu_line.ApplyerId=".$uid." LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="select count(*) as count from chu_line ,chu_trafficwarning WHERE chu_line.Name=chu_trafficwarning.LineNo and  chu_line.ApplyerId=".$uid;
			}
			
			//p($sql);die;
			$rank=$db->query($sql);
			$count=$db->query($sql_count);
			$thead=array();
			$thead['Rank']="排名";
			$thead['Name']="线路名";
			$thead['MinTraffic']="最小值(%)";
			$thead['MaxTraffic']="最大值(%)";
			$tr=array();
			//$i=1;
			$i=($current_page-1)*$item_everypage+1;
			foreach ($rank as $k => $v) {
				$t=array();
				$t["Rank"]=$i;
				$t["Name"]=$v["Name"];
				$t["Id"]=$v["Id"];
				$t["MinTraffic"]=$v["MinTraffic"];
				$t["MaxTraffic"]=$v["MaxTraffic"];
				$t["Describe"]=$v["Describe"];
				
				$tr[]=$t;
				$i=$i+1;
			}
			$table=array("thead"=>$thead,"tr"=>$tr);
			if($Type!="客户")
			{
				$table['operate']=$operate;
			}
			$data=array("table"=>$table);
			$data['item_count']="".$count[0]["count"];
			$db->close();
			return $data;
		}
		//得到组网性能排名
		public function getNetworkPerformance($region_id,$Type,$uid,$current_page,$item_everypage){
			$db=new DB();
			if($Type=="客户")
			{
				$sql="select chu_line.Name,chu_line.Describe,chu_networkperformance.ErrorRate,chu_networkperformance.NoSecond,chu_networkperformance.LuminousPower,chu_networkperformance.Id from chu_line ,chu_networkperformance WHERE chu_line.Name=chu_networkperformance.LineNo and  chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.") order by chu_networkperformance.ErrorRate desc ,chu_networkperformance.NoSecond desc LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
			$sql_count="select count(*) as count from chu_line ,chu_networkperformance WHERE chu_line.Name=chu_networkperformance.LineNo and  chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.")";
				
			}
			else
			{
				$sql="select chu_line.Name,chu_line.Describe,chu_networkperformance.ErrorRate,chu_networkperformance.NoSecond,chu_networkperformance.LuminousPower,chu_networkperformance.Id from chu_line ,chu_networkperformance WHERE chu_line.Name=chu_networkperformance.LineNo and chu_networkperformance.LineNo in (select Name from chu_line WHERE ApplyerId in (select uid from 						
				chu_user where region_id=".$region_id.")) order by chu_networkperformance.ErrorRate desc ,chu_networkperformance.NoSecond desc LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="select count(*) as count from chu_line ,chu_networkperformance WHERE chu_line.Name=chu_networkperformance.LineNo and chu_networkperformance.LineNo in (select Name from chu_line WHERE ApplyerId in (select uid from 						
				chu_user where region_id=".$region_id."))";
			}
			
			$rank=$db->query($sql);
			$count=$db->query($sql_count);
			$thead=array();
			$thead['Rank']="排名";
			$thead['Name']="线路名";
			$thead['ErrorRate']="误码率";
			$thead['NoSecond']="不可用秒";
			$thead['LuminousPower']="光功率";
			$tr=array();
			//$i=1;
			$i=($current_page-1)*$item_everypage+1;
			foreach ($rank as $k => $v) {
				$t=array();
				$t["Rank"]=$i;
				$t["Name"]=$v["Name"];
				$t["ErrorRate"]=$v["ErrorRate"];
				$t["NoSecond"]=$v["NoSecond"];
				$t["LuminousPower"]=$v["LuminousPower"];
				$t["Describe"]=$v["Describe"];
				$t['Id']=$v['Id'];
				$tr[]=$t;
				$i=$i+1;
			}
			$table=array("thead"=>$thead,"tr"=>$tr);
			if($Type!="客户")
			{
				$operate=array("delete"=>true);
				$table['operate']=$operate;
				
			}
			$data=array("table"=>$table);
			$data['item_count']="".$count[0]["count"];
			$db->close();
			return $data;
		}
		//得到ip性能排名
		public function getIpPerformance($region_id,$Type,$uid,$current_page,$item_everypage){
			$db=new DB();
			if($Type=="客户")
			{
				$sql="select chu_line.Name,chu_line.Describe,chu_ipperformance.PacketLossRate,chu_ipperformance.Twitch,chu_ipperformance.Delay,chu_ipperformance.Id from chu_line ,chu_ipperformance WHERE chu_line.Name=chu_ipperformance.LineNo and  chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.") order by chu_ipperformance.PacketLossRate desc,chu_ipperformance.Twitch desc ,chu_ipperformance.Delay desc LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="select count(*) as count from chu_line ,chu_ipperformance WHERE chu_line.Name=chu_ipperformance.LineNo and  chu_line.Name in(SELECT Name from chu_line where ApplyerId=".$uid.")";
			}
			else
			{
				$sql="SELECT 	 chu_line.Name,chu_line.Describe,chu_ipperformance.PacketLossRate,chu_ipperformance.Twitch,chu_ipperformance.Delay,chu_ipperformance.Id FROM chu_line,chu_ipperformance WHERE
	chu_line. NAME = chu_ipperformance.LineNo AND chu_ipperformance.LineNo IN  (SELECT 	NAME FROM chu_line	WHERE	ApplyerId IN (SELECT uid FROM chu_user	WHERE	region_id =".$region_id.")) order by chu_ipperformance.PacketLossRate desc,chu_ipperformance.Twitch desc ,chu_ipperformance.Delay desc LIMIT ".($current_page-1)*$item_everypage.",".$item_everypage;
				$sql_count="SELECT 	count(*) as count FROM chu_line,chu_ipperformance WHERE
	chu_line.NAME = chu_ipperformance.LineNo AND chu_ipperformance.LineNo IN  (SELECT 	NAME FROM chu_line	WHERE	ApplyerId IN (SELECT uid FROM chu_user	WHERE	region_id =".$region_id."))";
			}
			
			//p($sql);die;
			
			$rank=$db->query($sql);
			$count=$db->query($sql_count);
			
			$thead=array();
			$thead['Rank']="排名";
			$thead['Name']="线路名";
			$thead['PacketLossRate']="丢包(%)";
			$thead['Twitch']="抖动(< ms)";
			$thead['Delay']="时延(< ms)";
			$tr=array();
			$i=($current_page-1)*$item_everypage+1;
			foreach ($rank as $k => $v) {
				$t=array();
				$t["Rank"]=$i;
				$t["Name"]=$v["Name"];
				$t["PacketLossRate"]=$v["PacketLossRate"];
				$t["Twitch"]=$v["Twitch"];
				$t["Delay"]=$v["Delay"];
				$t["Describe"]=$v["Describe"];
				$t['Id']=$v['Id'];
				$tr[]=$t;
				$i=$i+1;
			}
			$table=array("thead"=>$thead,"tr"=>$tr);
			if($Type!="客户")
			{
			 	$operate=array("delete"=>true);
				$table['operate']=$operate;
				
			}
			$data=array("table"=>$table);
			$data['item_count']="".$count[0]["count"];
			$db->close();
			return $data;
		}
		
		

		//得到某条线路的详细信息
		public function getLineDetail($Id){
			$db=new DB();
			$sql="SELECT *  from chu_line,chu_user where chu_line.ApplyerId=chu_user.uid and chu_line.Id=".$Id;
			$line=$db->getOne($sql);
			$db->close();
			return $line;
		}

		
	   //创建线路
	   public function createLine($line,$mangerId){
		   $db=new DB();
		   $sql="insert into chu_line values('',";
		   foreach($line as $k=>$v)
		   {
			   $sql=$sql."'".$v."',";
		   }
		   $sql=substr_replace($sql, ')', -1);
		   

		   //p($sql);die;
		   if($db->update($sql))
		   {
			 $sql_getLineId="select * from chu_line where Name='".$line['Name']."'";
		   	 //echo $sql_getLineId;
			 $lineId=array();
		  	 $lineId=$db->getOne($sql_getLineId);
			 $Id=$lineId['Id']; 
			 //P($Id);
			 

		  
	
			
			
			$this->message("申请线路成功!");
			   
		   }
		   else
		   {
			   $this->message("操作失败!");
		   }
		   return $this;
	   }
	   
	   public function initLinecreate(){
		   $db=new DB();
		   $data=array('province'=>getRegion(1111,1));
		   $db->close();
		   return $data;
	   }
	   
	   public function updateInfo($data,$token)
	   {
		   $db=new DB();
		   $sql="update chu_user set ";
		   foreach ($data as $k => $v) {
			   $sql=$sql.$k."='".$v."',";
		   }
		   $sql=substr($sql,0,-1);
		   $filter=" where token='".$token."'";
		   $sql=$sql.$filter;
		   if($db->update($sql))
		   {
			   $this->message('添加成功');
		   }
		   else
		   {
			   $this->message('添加错误');
		   }
		   $db->close();
		   return $this;
	   }
	   
	   public function addresource($data)
	   {
		   p($data);die;
	   }
	   

		

		//Add a department into plevel
		public function addDepartment($plevel,$name){
			$db = new DB();
			$sql = "SELECT max(level) as maxlevel FROM chu_department WHERE level like '".$plevel."__' order by level asc";		
			$maxleveltemp = $db->getOne($sql);
			$maxlevel =$maxleveltemp['maxlevel'];
			$newlevel = (int)substr($maxlevel,strlen($plevel))+1;

			if($newlevel<10)
				$newlevel = $plevel."0".$newlevel;
			else
				$newlevel = $plevel.$newlevel;
			$id = makeGuid();

			$sql = "INSERT INTO chu_department VALUES('{id}','{name}','{descript}','{level}')";
			//目前没有描述
			$sql=str_replace("{id}",$id,$sql);
			$sql=str_replace("{name}",$name,$sql);
			$sql=str_replace("{descript}",'',$sql);
			$sql=str_replace("{level}",$newlevel,$sql);
			if($db->update($sql))
				$this->message("添加成功")->end();
			else
				$this->error("添加失败")->end();
			$db->close();
		}

		//获取部门列表
		public function getDepartment($level='')
		{
			$sql = "SELECT * FROM chu_department WHERE level LIKE '".$level."%'  order by level asc";
			$db = new DB();
			$departments = $db->query($sql);
			$db->close();
			$departmentlist = array();
			foreach ($departments as $key => $value) {
				$level_count = strlen($value['level'])/2;
				if($level_count <= 1)
					$departmentlist[$value['level']] = array(
						'id' => $value['id'],
						'text' => $value['name'],
						'level' => $value['level']
					);
				else {
					$tmp = &$departmentlist;
					for ($item=1; $item < $level_count; $item++) {
						$tmp = &$tmp[substr($value['level'],0,$item*2)];
						if(isset($tmp['children'])){
							$tmp = &$tmp['children'];
						}
						else{
							$tmp['children'] = array();
							$tmp = &$tmp['children'];
						}
					}
					$tmp[$value['level']] = array(
						'id' => $value['id'],
						'text' => $value['name'],
						'level' => $value['level']
					);
				}
			}
			$this->data($departmentlist)->end();
		}

		// get info of department
		public function getDepdetail($level){
			if(empty($level))return;
			$db = new DB();

			$sql = "SELECT id,name,level FROM chu_department WHERE level='".$level."'";		
			$depinfo = $db->getOne($sql);
			$sql = "SELECT uid,uname,email FROM chu_user WHERE department_id='".$depinfo["id"]."'";		
			$depusers = $db->query($sql);
			$rs = array(
				'depinfo' => &$depinfo,
				'redirectUrl'=>"Department/detail/?level=".$level,
				'depusers' => &$depusers
			);
			$this->data($rs)->end();
		}

		//Delete department
		public function delDepartment($depid){
			$db = new DB();

			$sql = "DELETE FROM chu_department WHERE level LIKE '".$depid."%'";
			if($db->update($sql))
				$this->message("删除成功")->end();
			else
				$this->error("删除失败")->end();
			$db->close();
		}
		

		// add notice @scott
		public function addnotice($title,$context,$region_id){
			if(empty($title))return;
			if(empty($context))return;
			if(empty($region_id))return;
			$db = new DB();
			$sql = "INSERT INTO chu_notice (Title, Context,CreateTime,DATETIME,Region_id) VALUES('{Title}','{Context}','{CreateTime}','{DateTime}','{Region_id}')";
			$sql=str_replace("{Title}",$title,$sql);
			$sql=str_replace("{Context}",$context,$sql);
			$sql=str_replace("{CreateTime}",time(),$sql);
			$sql=str_replace("{DateTime}",time(),$sql);
			$sql=str_replace("{Region_id}",$region_id,$sql);
			if($db->update($sql)){
				$this->message("添加成功");
				$rs = array(
				'sql'=>$sql,
				'redirectUrl'=>"/WorkOrder/noticeinput/",
				);
				$this->data($rs)->end();
			}
			else
				$this->error($sql)->end();
			$db->close();
		}
		//@allen3039 公告删除
		public function deleteNotice($Id){
			$sql="delete from chu_notice where Id=".$Id;
			$db=new DB();
			if($db->update($sql)){
				$this->message("删除成功!");
			}
			else{
				$this->message("删除失败!");
			}
			return $this;
			
		}
		
		//@allen3039 流量告警删除
		public function deleteTraffic($Id)
		{
			$sql="delete from chu_trafficwarning where Id=".$Id;
			//p($sql);die;
			$db=new DB();
			if($db->update($sql)){
				$this->message("删除成功!");
			}
			else{
				$this->message("删除失败!");
			}
			return $this;			
		}
		//@allen3039 ip性能数据删除
		public function deleteIPperformance($Id)
		{
			$sql="delete from chu_ipperformance where Id=".$Id;
			//p($sql);die;
			$db=new DB();
			if($db->update($sql)){
				$this->message("删除成功!");
			}
			else{
				$this->message("删除失败!");
			}
			return $this;	
		}
		public function deleteNetworkperformance($Id)
		{
			$sql="delete from chu_networkperformance where Id=".$Id;
			//p($sql);die;
			$db=new DB();
			if($db->update($sql)){
				$this->message("删除成功!");
			}
			else{
				$this->message("删除失败!");
			}
			return $this;	
			
		}
		
		//客户信息录入判别
		public function getDifUserinfo($uid,$userType)
		{
			$db=new DB();
			$sql="";
			if ($userType=="客户") {
				$sql="SELECT * from chu_user where uid='".$uid."'";
			} else {
				$sql="SELECT uid,upw,uname,email,salt,verify,register_ip,register_date,last_ip,last_login,token,department_id,region_id,role_id,Type from chu_user where uid='".$uid."'";
			}
			$difinfo=$db->query($sql);
			$db->close();
			return $difinfo;
		}
		
		


		
	   //IP性能添加
	   public function addIPpfrmc($Name,$PacketLossRate,$Twitch,$Delay){
		   $db=new DB();
		   $sql="insert into chu_ipperformance(LineNo,PacketLossRate,Twitch,Delay) 
		   values('".$Name."',".$PacketLossRate.",".$Twitch.",".$Delay.")";

		   //p($sql);die;
		   if($db->update($sql))
		   {  
	
			
			
			$this->message("IP性能录入成功!");
			   
		   }
		   else
		   {
			   $this->message("IP性能录入失败!");
		   }
		   return $this;
	   }
	   
	   //组网性能录入
	   public function addntwrk($Name,$ErrorRate,$NoSecond,$LuminousPower)
	   {
		   
		     $db=new DB();
		   $sql="insert into chu_networkperformance(LineNo,ErrorRate,NoSecond,LuminousPower) 
		   values('".$Name."',".$ErrorRate.",".$NoSecond.",'".$LuminousPower."')";

		   //p($sql);die;
		   if($db->update($sql))
		   {  
	
			
			
			$this->message("组网性能录入成功!");
			   
		   }
		   else
		   {
			   $this->message("组网性能录入失败!");
		   }
		   return $this;
	   }
	   
	   //流量告警录入
	   public function addtrfcwr($Name,$MinTraffic,$MaxTraffic)
	   {
		   
		   $db=new DB();
		   $sql="insert into chu_trafficwarning(LineNo,MinTraffic,MaxTraffic) 
		   values('".$Name."',".$MinTraffic.",".$MaxTraffic.")";

		   //p($sql);die;
		   if($db->update($sql))
		   {  
	
			
			
			$this->message("流量告警录入成功!");
			   
		   }
		   else
		   {
			   $this->message("流量告警录入失败!");
		   }
		   return $this;
	   }

		//拓扑图管理
		public function topologyManage(){
			$db=new DB();
			$type=array("topoElectric","topoGeography","srcWorksheet","serviceRoute");
			$name=array("电路拓扑","地理拓扑","资然工资表","业务路由");
			$all=array();
			for ($i=0; $i < 4; $i++) { 
				$sql="SELECT count(*) count from chu_image where ImageType='".$type[$i]."'";
				$result=$db->query($sql);
				$count=$result[0]['count'];
				$arr=array("name"=>$name[$i],"type"=>$type[$i],"count"=>$count);
				$all[]=$arr;
			}
			$thead=array("名称","图片数量","操作");
			$table=array("thead"=>$thead,"tr"=>$all);
			$data=array("table"=>$table);
			$data['item_count']="".count($all);
			$db->close();
			return $data;

		}

		//对拓扑中的图片进行管理
		public function imageManage($type){
			$db=new DB();
			$sql="SELECT * from chu_image where ImageType='".$type."' order by Sort";
			$imagelist=$db->query($sql);
			$tr=array();
			foreach ($imagelist  as $v) {
				$arr=array("Id"=>$v['Id'],"ImageUrl"=>$v['ImageUrl'],"ImageName"=>$v['ImageName'],"Sort"=>$v['Sort']);
				$tr[]=$arr;
			}
			$thead=array("图片名称","操作","排序");
			$table=array("thead"=>$thead,"tr"=>$tr);
			$data=array("table"=>$table,"item_count"=>"".count($tr));
			$db->close();
			return $data;

		}

		//删除图片
		public function deleteImage($Id){
			$db=new DB();
			$sql="SELECT * from chu_image where Id=".$Id;
			$image=$db->getOne($sql);
			$type=$image['ImageType'];
			$sort=$image['Sort'];
			if($db->update("DELETE from chu_image where Id=".$Id)){
				$db->update("update chu_image set Sort=Sort-1 where ImageType='".$type."' and Sort>".$sort);
				$db->close();
				unlink("../../".$image['ImageUrl']);
				$arr = array('redirectUrl'=>"/Line/imagemanage?type=".$type);
				$this->data($arr);
				$db->close();
				$this->message("删除成功!")->end();
			}else{
				$arr = array('redirectUrl'=>"/Line/imagemanage?type=".$type);
				$this->data($arr);
				$db->close();
				$this->message("删除失败!")->end();
			}
		}

		//对图片进行排序
		public function imageSort($Id,$type){
			//p($type);die;
			$db=new DB();
			$sql="SELECT * from chu_image where Id=".$Id;
			$image=$db->getOne($sql);
			$imagetype=$image['ImageType'];
			if("up" == $type){
				$before=$db->getOne("SELECT * from chu_image where Id!=".$image['Id']." and ImageType='".$imagetype."' and Sort<=".$image['Sort']." order by Sort desc limit 1");
				$db->update("update chu_image set Sort=".$before['Sort']." where Id=".$image['Id']);
				$db->update("update chu_image set Sort=".$image['Sort']." where Id=".$before['Id']);
			}

			if("down" == $type){
				$after=$db->getOne("SELECT * from chu_image where Id!=".$image['Id']." and ImageType='".$imagetype."' and Sort>=".$image['Sort']." order by Sort asc limit 1");
				$db->update("update chu_image set Sort=".$after['Sort']." where Id=".$image['Id']);
				$db->update("update chu_image set Sort=".$image['Sort']." where Id=".$after['Id']);
			}
			$db->close();
			$arr = array('redirectUrl'=>"/Line/topologymanage/imagemanage?type=".$imagetype);
			$this->data($arr)->end();
		}

		//得到处理员列表
		public function getHandlerList($token,$Id){
			$db=new DB();
			$user=$db->getOne("SELECT * from chu_user where token='".$token."'");
			$region=$user['region_id'];
			$handlers=$db->query("SELECT uid,uname from chu_user where role_id=4 and region_id=".$region);
			$data=array("handlers"=>$handlers,"Id"=>$Id,"dispatcherId"=>$user['uid']);
			$db->close();
			return $data;
		}


		//对工单进行调度
		public function workorderDispatch($Id,$handlerId,$dispatcherId){
			$db=new DB();

			//先更改工单状态
			$update="update chu_workorder set Status='未处理' where Id=".$Id;
			if($db->update($update)){
				//再添加路由信息（调度和待处理）
				$sql1="insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$Id."','".$dispatcherId."','调度','".Time()."')";
				$sql2="insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$Id."','".$handlerId."','待处理','".Time()."')";
				$db->update($sql1);
				$db->update($sql2);
				$arr = array('redirectUrl'=>"/WorkOrder/overview");
				$this->message("调度成功!");
				$db->close();
				$this->data($arr)->end();
			}else{
				$arr = array('redirectUrl'=>"/WorkOrder/overview");
				$this->message("调度失败!");
				$this->data($arr)->end();
			}
		}

		//退回工单
		public function workorderReturn($token,$Id){
			$db=new DB();
			$user=$db->getOne("SELECT * from chu_user where token='".$token."'");
			//先更改工单状态
			$update="update chu_workorder set Status='未调度' where Id=".$Id;
			if($db->update($update)){
				//再添加路由信息（重新调度）
				$dispatcher=$db->getOne("SELECT * from chu_user where region_id='".$user['region_id']."' and role_id='5' limit 1");
				$db->update("update chu_routelog set Flag=1 where WorkOrderId=".$Id." and Result='待处理'");
				$db->update("insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$Id."','".$user['uid']."','退回','".Time()."')");
				$db->update("insert into chu_routelog(WorkOrderId,HandlerId,Result,Time) value('".$Id."','".$dispatcher['uid']."','待调度','".Time()."')");

				$arr = array('redirectUrl'=>"/WorkOrder/overview");
				$this->message("退回成功!");
				$db->close();
				$this->data($arr)->end();
			}else{
				$arr = array('redirectUrl'=>"/WorkOrder/overview");
				$this->message("退回失败!");

				$this->data($arr)->end();
			}
		}
		

		//取消工单
		public function workorderCancel($Id){
			$db=new DB();
			$workorder=$db->getOne("SELECT * from chu_workorder where Id=".$Id);
			//应该向相关人员推送工单取消消息

			//删除该条工单的相关信息
			if($db->update("delete from chu_workorder where Id=".$Id)){
				$db->update("delete from chu_routelog where WorkOrderId=".$Id);
				$arr = array('redirectUrl'=>"/Line/listworkorder?lineId=".$workorder['LineId']);
				$this->message("工单".$workorder['Id']."取消成功!");
				$db->close();
				$this->data($arr)->end();
			}else{
				$arr = array('redirectUrl'=>"/Line/listworkorder?lineId=".$workorder['LineId']);
				$this->message("取消失败!");
				$db->close();
				$this->data($arr)->end();
			}
		}

		//得到某个地区的全部线路
		public function getLineRegion($region_id){
			$db=new DB();
			$sql="SELECT * from chu_line where ApplyerId in (select uid from chu_user where region_id=".$region_id.")";
			$data=$db->query($sql);
			//p($data);die;
			$this->data($data)->end();
		}

	}
?>